const { db, query } = require("../database/db")
// 报名列表
exports.list = async (page, pageSize, userName, activeName) => {
    let listSql = `
        SELECT 
            active_users.id,active_users.active_id,active_users.user_id,active_users.agree_time,
            actives.name AS active_name, users.username , users.realname
        FROM active_users 
        JOIN users ON users.id = active_users.user_id
        JOIN actives ON actives.id = active_users.active_id
    `
    // 初始化参数数组
    let params = [];

    // 如果有搜索条件，添加到SQL语句中
    let conditions = [`active_users.isDeleted = 'false' AND active_users.isRevoke = 'false'`]; // 默认条件，用户状态为0
    if (userName) {
        conditions.push(`users.username LIKE ?`);
        params.push(`%${userName}%`);
    }
    if (activeName) {
        conditions.push(`actives.name LIKE ?`);
        params.push(`%${activeName}%`);
    }
    // 如果有搜索条件，添加WHERE子句
    if (conditions.length > 0) {
        listSql += ` WHERE ${conditions.join(' AND ')}`;
    }
    // 添加分页的SQL语句
    listSql += ` LIMIT ? , ?`;
    // (page - 1) * pageSize, page * pageSize
    // 添加分页参数
    params.push((page - 1) * pageSize);
    params.push(pageSize);
    // 计算总数的SQL语句
    let countSql = `SELECT COUNT(*) as count FROM active_users`;
    if (conditions.length > 0) {
        countSql += ` WHERE ${conditions.map(c => `(${c})`).join(' AND ')}`;
    }
    // 执行查询
    const [usersResult, countResult] = await Promise.all([
        query(listSql, params), // 查询用户列表
        query(countSql, params) // 查询总数
    ]);
    console.log("结果：：：", usersResult)
    return {
        users: usersResult, // 用户列表
        total: countResult // 总数
        // total: countResult.total[0].count
    };
}

// 撤销报名
exports.revoke = async (id) => {
    let sql = `
        UPDATE active_users SET isRevoke = 'true' 
        WHERE id = ?
    `
    return query(sql, [id])
}

// 添加报名记录---before----检查是否已经报过名了
exports.check = async (active_id, user_id) => {
    let sql = `
        SELECT * FROM active_users
        WHERE isRevoke = 'false' AND isDeleted = 'false' AND active_id = ? AND user_id = ? 
    `
    return query(sql, [active_id, user_id])
}

// 添加报名记录---用户报名
exports.add = async (active_id, user_id) => {
    let sql = `
        INSERT INTO active_users (active_id,user_id) VALUES (?,?)

    `
    return query(sql,[active_id, user_id])
}

// 获取报名记录
exports.get = async (id) => {
    let sql = `
        SELECT 
            active_users.id,active_users.active_id,active_users.user_id,active_users.agree_time,
            actives.name AS active_name, users.username, users.realname
        FROM active_users 
        JOIN users ON users.id = active_users.user_id
        JOIN actives ON actives.id = active_users.active_id
        WHERE  active_users.id = ?
    `
    return query(sql,[id])
}

// 删除报名记录
exports.delete = async (id) => {
    let sql = `
        DELETE FROM active_users 
        WHERE id = ?
    `
    return query(sql,[id])
}